const mysql = require('mysql2/promise');
const config = require('./database-config.js');

/**
 * 宝石系统数据验证脚本
 * 验证导入的宝石基础数据和属性数据是否正确
 */

async function verifyGemSystemData() {
    let connection;

    try {
        console.log('🔍 开始验证宝石系统数据...');

        // 连接数据库
        connection = await mysql.createConnection(config.source);
        console.log('✅ 数据库连接成功');

        // 1. 验证宝石基础表
        console.log('\n1️⃣ 验证宝石基础表...');
        const [gemBaseCount] = await connection.execute('SELECT COUNT(*) as count FROM `gem_base`');
        console.log(`📊 宝石基础表记录数: ${gemBaseCount[0].count}`);

        if (gemBaseCount[0].count !== 15) {
            console.log('⚠️ 警告: 宝石基础表记录数不是预期的15条');
        }

        // 显示所有宝石基础数据
        const [gemBaseData] = await connection.execute('SELECT * FROM `gem_base` ORDER BY `id`');
        console.log('\n📋 宝石基础数据:');
        gemBaseData.forEach(gem => {
            console.log(`  ${gem.id}. ${gem.name} - 图片:${gem.image}, 最大等级:${gem.max_level}`);
        });

        // 2. 验证宝石属性表
        console.log('\n2️⃣ 验证宝石属性表...');
        const [gemAttributesCount] = await connection.execute('SELECT COUNT(*) as count FROM `gem_attributes`');
        console.log(`📊 宝石属性表记录数: ${gemAttributesCount[0].count}`);

        // 显示所有宝石属性数据
        const [gemAttributesData] = await connection.execute(`
      SELECT ga.*, gb.name as gem_name 
      FROM \`gem_attributes\` ga 
      JOIN \`gem_base\` gb ON ga.gem_id = gb.id 
      ORDER BY ga.gem_id, ga.attribute_name
    `);

        console.log('\n📋 宝石属性数据:');
        let currentGemId = null;
        gemAttributesData.forEach(attr => {
            if (currentGemId !== attr.gem_id) {
                console.log(`\n  💎 ${attr.gem_name} (ID:${attr.gem_id}):`);
                currentGemId = attr.gem_id;
            }
            console.log(`    - ${attr.attribute_name}: 最大${attr.max_value}, 槽位${attr.equipment_slots}`);
        });

        // 3. 验证属性统计
        console.log('\n3️⃣ 验证属性统计...');
        const [attributeStats] = await connection.execute(`
      SELECT attribute_name, COUNT(*) as gem_count, MAX(max_value) as max_value
      FROM \`gem_attributes\` 
      GROUP BY attribute_name 
      ORDER BY attribute_name
    `);

        console.log('\n📊 属性统计:');
        attributeStats.forEach(stat => {
            console.log(`  - ${stat.attribute_name}: ${stat.gem_count}种宝石, 最大${stat.max_value}`);
        });

        // 4. 验证装备槽位统计
        console.log('\n4️⃣ 验证装备槽位统计...');
        const [slotStats] = await connection.execute(`
      SELECT equipment_slots, COUNT(*) as count
      FROM \`gem_attributes\` 
      GROUP BY equipment_slots 
      ORDER BY count DESC
    `);

        console.log('\n📊 装备槽位统计:');
        slotStats.forEach(stat => {
            const slotNames = stat.equipment_slots.split(',').map(slot => {
                const slotMap = { '1': '头盔', '2': '武器', '3': '铠甲', '4': '项链', '5': '护腕', '6': '鞋子' };
                return slotMap[slot] || slot;
            }).join(',');
            console.log(`  - ${slotNames} (${stat.equipment_slots}): ${stat.count}条属性`);
        });

        // 5. 验证特殊规则
        console.log('\n5️⃣ 验证特殊规则...');

        // 检查穿透率宝石限制
        const [penetrationGems] = await connection.execute(`
      SELECT ga.*, gb.name as gem_name 
      FROM \`gem_attributes\` ga 
      JOIN \`gem_base\` gb ON ga.gem_id = gb.id 
      WHERE ga.attribute_name = '穿透率'
    `);

        console.log('\n⚠️ 穿透率宝石限制检查:');
        penetrationGems.forEach(gem => {
            const slots = gem.equipment_slots.split(',');
            const hasRestrictedSlots = slots.includes('5') || slots.includes('6'); // 护腕或鞋子
            if (hasRestrictedSlots) {
                console.log(`  ❌ ${gem.gem_name}: 穿透率不应能镶嵌在护腕或鞋子上 (当前槽位: ${gem.equipment_slots})`);
            } else {
                console.log(`  ✅ ${gem.gem_name}: 穿透率槽位正确 (${gem.equipment_slots})`);
            }
        });

        // 6. 验证数据完整性
        console.log('\n6️⃣ 验证数据完整性...');

        // 检查是否有宝石没有属性
        const [gemsWithoutAttributes] = await connection.execute(`
      SELECT gb.* 
      FROM \`gem_base\` gb 
      LEFT JOIN \`gem_attributes\` ga ON gb.id = ga.gem_id 
      WHERE ga.gem_id IS NULL
    `);

        if (gemsWithoutAttributes.length > 0) {
            console.log('❌ 发现没有属性的宝石:');
            gemsWithoutAttributes.forEach(gem => {
                console.log(`  - ${gem.name} (ID:${gem.id})`);
            });
        } else {
            console.log('✅ 所有宝石都有对应的属性');
        }

        // 检查外键约束
        const [orphanedAttributes] = await connection.execute(`
      SELECT ga.* 
      FROM \`gem_attributes\` ga 
      LEFT JOIN \`gem_base\` gb ON ga.gem_id = gb.id 
      WHERE gb.id IS NULL
    `);

        if (orphanedAttributes.length > 0) {
            console.log('❌ 发现孤立的属性记录:');
            orphanedAttributes.forEach(attr => {
                console.log(`  - 宝石ID:${attr.gem_id}, 属性:${attr.attribute_name}`);
            });
        } else {
            console.log('✅ 所有属性记录都有对应的宝石');
        }

        console.log('\n🎉 宝石系统数据验证完成！');

        // 总结
        console.log('\n📊 验证总结:');
        console.log(`  - 宝石类型: ${gemBaseCount[0].count} 种`);
        console.log(`  - 宝石属性: ${gemAttributesCount[0].count} 条`);
        console.log(`  - 属性类型: ${attributeStats.length} 种`);
        console.log(`  - 装备槽位组合: ${slotStats.length} 种`);
        console.log(`  - 数据完整性: ${gemsWithoutAttributes.length === 0 && orphanedAttributes.length === 0 ? '✅ 正常' : '❌ 异常'}`);

    } catch (error) {
        console.error('❌ 验证失败:', error.message);
        if (error.code) {
            console.error('错误代码:', error.code);
        }
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 执行验证
if (require.main === module) {
    verifyGemSystemData()
        .then(() => {
            console.log('\n✅ 宝石系统数据验证脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('\n❌ 宝石系统数据验证脚本执行失败:', error.message);
            process.exit(1);
        });
}

module.exports = { verifyGemSystemData };
